package com.xdwanj.dao.impl;

import com.xdwanj.dao.BaseDao;
import com.xdwanj.dao.PetDao;
import com.xdwanj.pojo.Pet;
import com.xdwanj.pojo.User;
import com.xdwanj.pojo.VacHistory;
import com.xdwanj.util.ConstantsVal;
import com.xdwanj.util.PageBean;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.Collections;
import java.util.Comparator;
import java.util.List;

public class PetDaoImpl implements PetDao {
    private BaseDao baseDao = new BaseDao();
    JdbcTemplate jdbcTemplate = baseDao.getJdbcTemplate();

    @Override
    public PageBean<Pet> findAll() {
        String sql = "select * from pet";
        String sqlNum = "select count(*) from pet";
        PageBean customerPageDto = baseDao.getCustomerPageDto(sql, null, new BeanPropertyRowMapper<Pet>(Pet.class), sqlNum, null, 1, ConstantsVal.PAGE_SIZE);
        return customerPageDto;
    }

    @Override
    public PageBean<Pet> findUserByPetName(String name, int pageSize, int currentPageNo) throws Exception {
        StringBuffer sql = new StringBuffer(" where p.u_id = up.u_id and up.u_id = u.id and p.name like '%");

        System.out.println((new StringBuffer("select *,p.id pet_id from user u,pet p,user_pet up").append(sql).append(name).append("%'")));
        System.out.println(new StringBuffer("select * from user u,pet p,user_pet up ").append(sql).append(name).append("%'"));

        PageBean<Pet> customerPageDto = baseDao.getCustomerPageDto(new StringBuffer("select *,p.id pet_id from user u,pet p,user_pet up").append(sql).append(name).append("%'").toString(),
                null, new BeanPropertyRowMapper<Pet>(Pet.class),
                new StringBuffer("select count(*) from user u,pet p,user_pet up ").append(sql).append(name).append("%'").toString(),
                null, currentPageNo,
                pageSize);
        System.out.println(customerPageDto);
        List<User> users = jdbcTemplate.query(new StringBuffer("select *,p.id pet_id from pet p,user u,user_pet up").append(sql).append(name).append("%'").toString(), new BeanPropertyRowMapper<User>(User.class));
        List<Pet> pets = customerPageDto.getContent();
        System.out.println(users);
        for (User user : users) {
            for (int i = 0; i < pets.size(); i++) {
                System.out.println(pets.get(i).getId());
                if (user.getPetId().equals(pets.get(i).getId())) {
                    pets.get(i).setUser(user);
                }
            }
        }
        return customerPageDto;
    }

    @Override
    public PageBean<Pet> findPetListByName(String name, int pageSize, int currentPageNo) throws Exception {
        if (name == null) {
            name = "";
        }

        name = name.trim();
        StringBuffer Name = new StringBuffer(name);
        for (int i = 0; i < name.length(); i++) {
            if (name.charAt(i) == ' ') {
                Name.setCharAt(i, '%');
            }
        }
        name = Name.toString();
        String sql = "select *,u.id userId from user u,pet p,user_pet up where p.id = up.p_id and up.u_id = u.id and p.name LIKE '%" + name + "%'";
        String sqlNum = "select count(*) from pet where pet.name LIKE '%" + name + "%'";
        PageBean customerPageDto = baseDao.getCustomerPageDto(sql, null, new BeanPropertyRowMapper<Pet>(Pet.class), sqlNum, null, currentPageNo, pageSize);
        List<Pet> pets = customerPageDto.getContent();
        Collections.sort(pets, new Comparator<Pet>() {
            @Override
            public int compare(Pet name1, Pet name2) {
                int result = name1.getName().length() - name2.getName().length();
                return result;
            }
        });
        return customerPageDto;
    }

    @Override
    public PageBean<Pet> findVacHistoryByPetName(String name, int pageSize, int currentPageNo) throws Exception {
        StringBuffer sql = new StringBuffer(" where p.v_id = pv.v_id and pv.p_id = v.p_id and p.name like '%");
        System.out.println(new StringBuffer("select *,p.id pet_id from vac_history v,pet p,pet_vac_history pv").append(sql).append(name).append("%'"));
        PageBean<Pet> customerPageDto = baseDao.getCustomerPageDto(new StringBuffer("select *,p.id pet_id from vac_history v,pet p,pet_vac_history pv").append(sql).append(name).append("%'").toString(),
                null, new BeanPropertyRowMapper<Pet>(Pet.class),
                new StringBuffer("select count(*) from vac_history v,pet p,pet_vac_history pv ").append(sql).append(name).append("%'").toString(),
                null, currentPageNo,
                pageSize);
        System.out.println(customerPageDto);
        List<VacHistory> vac_history = jdbcTemplate.query(new StringBuffer("select *,p.id pet_id from pet p,vac_history v,pet_vac_history pv").append(sql).append(name).append("%'").toString(), new BeanPropertyRowMapper<VacHistory>(VacHistory.class));
        List<Pet> pets = customerPageDto.getContent();
        for (VacHistory vacHistory : vac_history) {
            for (int i = 0; i < pets.size(); i++) {
                System.out.println(pets.get(i).getId());
                if (vacHistory.getPetId().equals(pets.get(i).getId())) {
                    pets.get(i).setVacHistory(vacHistory);
                }
            }
        }
        return customerPageDto;
    }

    @Override
    public Pet findPetById(int id) throws Exception {
        String sql = "select * from pet where pet.id=?";
        Pet pet = new Pet();
        pet = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Pet>(), id);
        return pet;
    }

    @Override
    public int addPet(int u_id, Pet pet) throws Exception {
        String petSql = "insert into pet (name,age,pet_type,sex,weight,info,sterilization,level,expelling_parasite_date,u_id) values(?,?,?,?,?,?,?,?,?,?)";
        String upSql = "insert into user_pet (u_id) values (?)";
        int addRow = jdbcTemplate.update(petSql, pet.getName(), pet.getAge(), pet.getPetType(), pet.getSex(), pet.getWeight(), pet.getInfo(), pet.getSterilization(), pet.getLevel(), pet.getExpellingParasiteDate(), u_id);
        jdbcTemplate.update(upSql, u_id);
        return addRow;
    }

    @Override
    public int updatePet(Pet pet) throws Exception {
        String petSql = "update pet set name = ?,age = ?,pet_type = ?,sex = ?,weight = ?,info = ?,sterilization = ?,level = ?,expelling_parasite_date = ?,u_id = ? where id = ?";
        String userPetSql = "update user_pet set u_id = ?";
        int updateRow = jdbcTemplate.update(petSql, pet.getName(), pet.getAge(), pet.getPetType(), pet.getSex(), pet.getWeight(), pet.getWeight(), pet.getSterilization(), pet.getLevel(), pet.getExpellingParasiteDate(), pet.getUserId(), pet.getId());
        jdbcTemplate.update(userPetSql, pet.getUserId());
        return updateRow;
    }

    @Override
    public int deletePet(int id) throws Exception {
        String petSql = "delete from pet where id = ?";
        String upSql = "delete from user_pet  where user_pet.p_id = ?";
        int deleteRow = 0;
        deleteRow += jdbcTemplate.update(petSql, id);
        jdbcTemplate.update(upSql, id);
        return deleteRow;
    }
}
